package com.fengwk.support.util;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fengwk.support.exception.BaseException;
import com.fengwk.support.util.LoggerUtils.Logger;
import com.fengwk.support.util.PoiUtils.ExcelCell.Style;

/**
 * poi封装
 */
public class PoiUtils {
	
	private static final Logger LOG = LoggerUtils.getLogger(PoiUtils.class);
	
	private static ThreadLocal<Map<String, CellStyle>> cellStyleCacheThreadLocal = new ThreadLocal<Map<String, CellStyle>>();
	
	public static final String FONT_FAMILY_SONG = "宋体";
	public static final String FONT_FAMILY_BLACK = "黑体";

	public static final boolean FONT_BOLD_TRUE = true;
	public static final boolean FONT_BOLD_FALSE = false;
	
	private static final Integer DEFAULT_SHEET = 0;
	private static final String DEFAULT_FILL_NULL_TEXT = "";// 默认填充null的文本
	private static final short DEFAULT_EXCEL_FONT_SIZE = 12;
	private static final String SPLIT = "_";
	
	private PoiUtils() {
		throw new BaseException("can not instance " + getClass());
	}
	
	/**
	 * 解析Excel为sheet->单元格表
	 * 
	 * @param path
	 * @return
	 */
	public static Map<String, Table<String>> parseExcelWithTable(String path) {
		return parseExcelWithTable(IoUtils.buildInputStream(path));
	}
	
	/**
	 * 解析Excel为sheet->单元格表
	 * 
	 * @param file
	 * @return
	 */
	public static Map<String, Table<String>> parseExcelWithTable(File file) {
		return parseExcelWithTable(IoUtils.buildInputStream(file));
	}
	
	/**
	 * 解析Excel为sheet->单元格表
	 * 
	 * @param is
	 * @return
	 */
	public static Map<String, Table<String>> parseExcelWithTable(InputStream is) {
		Workbook wb = read(is);
		Iterator<Sheet> sheetIt = wb.sheetIterator();
		Map<String, Table<String>> tableMap = new LinkedHashMap<String, Table<String>>();
		while(sheetIt.hasNext()) {
			Sheet sheet = sheetIt.next();
			Table<String> table = new Table<String>();
			int maxRowNum = sheet.getLastRowNum();
			for(int rowNum = 0; rowNum <= maxRowNum; rowNum ++) {
				Row row = sheet.getRow(rowNum);
				if(row == null) continue;
				int maxColNum = row.getLastCellNum();
				for(int colNum = 0; colNum <= maxColNum; colNum ++) {
					Cell cell = sheet.getRow(rowNum).getCell(colNum);
					table.add(rowNum, colNum, getCellValue(cell, sheet, rowNum, colNum));
				}
			}
			tableMap.put(sheet.getSheetName(), table);
		}
		return tableMap;
	}
	
	public static Map<String, List<ExcelGrid>> parseExcelWithCol(String path, Integer beginRow, Integer beginCol) {
		return parseExcelWithCol(FileUtils.buildFile(path), beginRow, beginCol);
	}
	
	public static Map<String, List<ExcelGrid>> parseExcelWithCol(File file, Integer beginRow, Integer beginCol) {
		return parseExcelWithCol(IoUtils.buildInputStream(file), beginRow, beginCol);
	}
	
	public static Map<String, List<ExcelGrid>> parseExcelWithCol(InputStream is, Integer beginRow, Integer beginCol) {
		return parseExcelWithCol(is, beginRow, beginCol, DEFAULT_SHEET);
	}
	
	/**
	 * 纵向解析Excel
	 * 
	 * @param is
	 * @param beginRow 解析起始行
	 * @param beginCol 解析起始列
	 * @param sheetIndex 要解析的sheet编号
	 * @return
	 */
	public static Map<String, List<ExcelGrid>> parseExcelWithCol(InputStream is, Integer beginRow, Integer beginCol, Integer sheetIndex) {
		Workbook wb = read(is);
		if(wb == null) {
			return null;
		}
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Map<String, List<ExcelGrid>> map = buildExcelMapWithCol(sheet, beginRow, beginCol);
		IoUtils.close(is);
		return map;
	}
	
	public static List<Map<String, List<ExcelGrid>>> parseAllExcelWithCol(String path, Integer beginRow, Integer beginCol) {
		return parseAllExcelWithCol(FileUtils.buildFile(path), beginRow, beginCol);
	}
	
	public static List<Map<String, List<ExcelGrid>>> parseAllExcelWithCol(File file, Integer beginRow, Integer beginCol) {
		return parseAllExcelWithCol(IoUtils.buildInputStream(file), beginRow, beginCol);
	}
	
	/**
	 * 纵向所有解析Excel的所有sheet
	 * 
	 * @param is
	 * @param beginRow
	 * @param beginCol
	 * @return
	 */
	public static List<Map<String, List<ExcelGrid>>> parseAllExcelWithCol(InputStream is, Integer beginRow, Integer beginCol) {
		Workbook wb = read(is);
		if(wb == null) {
			return null;
		}
		List<Map<String, List<ExcelGrid>>> resList = new ArrayList<Map<String, List<ExcelGrid>>>();
		Iterator<Sheet> sheetIt = wb.iterator();
		while(sheetIt.hasNext()) {
			Sheet sheet = sheetIt.next();
			resList.add(buildExcelMapWithCol(sheet, beginRow, beginCol));
		}
		IoUtils.close(is);
		return resList;
	}
	
	public static Map<String, Map<String, List<ExcelGrid>>> parseAllMapExcelWithCol(InputStream is, Integer beginRow, Integer beginCol) {
		Workbook wb = read(is);
		if(wb == null) {
			return null;
		}
		Map<String, Map<String, List<ExcelGrid>>> resMap = new HashMap<String, Map<String, List<ExcelGrid>>>();
		Iterator<Sheet> sheetIt = wb.iterator();
		while(sheetIt.hasNext()) {
			Sheet sheet = sheetIt.next();
			resMap.put(sheet.getSheetName(), buildExcelMapWithCol(sheet, beginRow, beginCol));
		}
		IoUtils.close(is);
		return resMap;
	}
	
	// 将sheet解析为map
	private static Map<String, List<ExcelGrid>> buildExcelMapWithCol(Sheet sheet, Integer beginRow, Integer beginCol) {
		Cell cell = null;// 列
		String value = null;// 暂时存放
		List<ExcelGrid> list = null;
		Map<String, List<ExcelGrid>> map = new LinkedHashMap<String, List<ExcelGrid>>();
		int maxRowNum = sheet.getLastRowNum();// 最大行数
		int maxCellNum = getMaxCellNum(sheet);// 最大列数
		for (int col = beginCol; col <= maxCellNum; col ++) {
			list = new LinkedList<ExcelGrid>();
			String title = null;
			ExcelGrid root = null;
			for (int row = beginRow; row <= maxRowNum; row ++) {
				Row currentRow = sheet.getRow(row);
				if(currentRow == null) break;
				cell = currentRow.getCell(col);
				value = getCellValue(cell);
				if (row == beginRow) {
					title = value;
					root = ExcelGrid.build(null, row, col, value);
				} else {
					list.add(ExcelGrid.build(root, row, col, value));
				}
			}
			map.put(title, list);
		}
		return map;
	}
	
	/**
	 * 纵向合并单元格,该合并之前不能有单元格结构
	 * 
	 * @param is
	 * @return
	 */
	@Deprecated
	public static byte[] mergeExcelWithCol(InputStream is) {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		mergeExcelWithCol(is, out);
		byte[] bytes = out.toByteArray();
		IoUtils.close(out);
		return bytes;
	}
	
	/**
	 * 纵向合并单元格,该合并之前不能有单元格结构
	 * 
	 * @param is
	 * @param path
	 */
	@Deprecated
	public static void mergeExcelWithCol(InputStream is, String path) {
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(FileUtils.buildFile(path));
			mergeExcelWithCol(is, out);
		} catch (FileNotFoundException e) {
		    LOG.error(e);
			return;
		} finally {
			IoUtils.close(out);
		}
	}
	
	/**
	 * 纵向合并单元格,该合并之前不能有单元格结构
	 * 
	 * @param is
	 * @param out
	 */
	@Deprecated
	public static void mergeExcelWithCol(InputStream is, OutputStream out) {
		mergeExcelWithColSelective(is, out);
	}
	
	public static byte[] mergeExcelWithColSelective(InputStream is, Integer...mergeCols) {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		mergeExcelWithColSelective(is, out, mergeCols);
		byte[] bytes = out.toByteArray();
		IoUtils.close(out);
		return bytes;
	}
	
	public static void mergeExcelWithColSelective(InputStream is, String path, Integer...mergeCols) {
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(FileUtils.buildFile(path));
			mergeExcelWithColSelective(is, out, mergeCols);
		} catch (FileNotFoundException e) {
		    LOG.error(e);
			return;
		} finally {
			IoUtils.close(out);
		}
	}
	
	public static void mergeExcelWithColSelective(InputStream is, OutputStream out, Integer...mergeCols) {
		Workbook wb = read(is);
		if(wb == null) {
			return;
		}
		Iterator<Sheet> itSheet = wb.sheetIterator();
		while(itSheet.hasNext()) {
			Sheet sheet = itSheet.next();
			int maxRowNum = sheet.getLastRowNum();
			int maxCellNum = getMaxCellNum(sheet);
			for(int col = 0; col <= maxCellNum; col ++) {
				if(ArrayUtils.isNotBlank(mergeCols) && !ArrayUtils.contains(mergeCols, col)) {
					continue;
				}
				String bufValue = null;
				int startRow = 0;
				int row = 0;
				for(row = 0; row <= maxRowNum; row ++) {
					Cell cell = sheet.getRow(row).getCell(col);
					String value = getCellValue(cell);
					if(!ObjectUtils.equals(bufValue, value) || row == maxRowNum) {
						int firstRow = startRow;
						int lastRow = row == maxRowNum ? row : row - 1;
						if(lastRow > firstRow) {
							mergeGrid(firstRow, lastRow, col, col, bufValue, sheet, wb); 
						}
						bufValue = value;
						startRow = row;
					}
				}
			}
		}
		try {
			wb.write(out);
		} catch (IOException e) {
		    LOG.error(e);
		}
		IoUtils.close(is);
		IoUtils.close(out);
	}
	
	private static <V> String convertObjectValue(V value) {
		return value == null ? DEFAULT_FILL_NULL_TEXT : value.toString();
	}
	
	private static <V> void fillSheet(Sheet sheet, Row row, Cell cell, V value) {
		fillSheet(sheet, row, cell, convertObjectValue(value));
	}
	
	private static void fillSheet(Sheet sheet, Row row, Cell cell, String value) {
		cell.setCellValue(value);
	}
	
	private static Row getRow(Sheet sheet, int rownum) {
		Row row = sheet.getRow(rownum);
		if(row == null) {
			row = sheet.createRow(rownum);
		}
		return row;
	}
	
	private static Cell getCell(Row row, int cellnum) {
		Cell cell = row.getCell(cellnum);
		if(cell == null) {
			cell = row.createCell(cellnum);
		}
		return cell;
	}
	
	// 读取流中的Excel
	private static Workbook read(InputStream is) {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(is);
		} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
		    LOG.error(e);
		}
		return wb;
	}
	
	private static int getMaxCellNum(Sheet sheet) {
		int maxRowNum = sheet.getLastRowNum();// 最大行数
		int maxCellNum = 0;
		for(int i = 0; i <= maxRowNum; i ++) {
			Row row = sheet.getRow(i);
			if(row == null) break;
			int nowRowMaxCellNum = row.getLastCellNum();
			if(maxCellNum < nowRowMaxCellNum) {
				maxCellNum = nowRowMaxCellNum;
			}
		}
		return maxCellNum;
	}
	
	// 获取单元格值,并获取合并单元格值
	private static String getCellValue(Cell cell, Sheet sheet, int row, int col) {
		String cellValue = getCellValue(cell);
		if(StringUtils.isBlank(cellValue)) {
			return getMergedRegionValue(sheet, row, col);
		}
		return cellValue;
	}
	
	// 获取单元格值
	private static String getCellValue(Cell cell) {
        String cellvalue = null;
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellTypeEnum()) {
            // 如果当前Cell的Type为NUMERIC
            case NUMERIC: {
                short format = cell.getCellStyle().getDataFormat();
                // excel中的时间格式
                if(format == 14 || format == 31 || format == 57 || format == 58) {
                    double value = cell.getNumericCellValue();
                    Date date = DateUtil.getJavaDate(value);
                    cellvalue = DateUtils.format(date, DateUtils.YYYYMMDD);
                }
                // 判断当前的cell是否为Date
                else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                	// 先注释日期类型的转换,在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式
                    // 如果是Date类型则,取得该Cell的Date值
                	// 对2014-02-02格式识别不出是日期格式
                    Date date = cell.getDateCellValue();
                    cellvalue = DateUtils.format(date, DateUtils.YYYYMMDD);
                } else {
                	// 如果是纯数字,取得当前Cell的数值
                    cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getStringCellValue().replaceAll("'", "''");
                break;
            case  BLANK:
                cellvalue = null;
                break;
			default:
				break;
            }
        }
        return cellvalue;  
    }
	
	private static <K, V> void mergeGrid(int firstRow, int lastRow, int firstCol, int lastCol, ExcelCell<K, V> excelCell, Sheet sheet, Workbook wb) {
		if(excelCell != null) {
			Cell cell = mergeGrid(firstRow, lastRow, firstCol, lastCol, convertObjectValue(excelCell.getValue()), sheet, wb);
			setCellStyle(wb, cell, excelCell);
		}
	}
	
	private static Cell mergeGrid(int firstRow, int lastRow, int firstCol, int lastCol, String value, Sheet sheet, Workbook wb) {
		CellRangeAddress cra=new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
		sheet.addMergedRegion(cra);
		Cell cell = sheet.getRow(firstRow).getCell(firstCol);
		cell.setCellValue(value);
		return cell;
	}
	
	// 获取合并单元格的值   
	private static String getMergedRegionValue(Sheet sheet, int row, int col){    
	    int sheetMergeCount = sheet.getNumMergedRegions();
	    for(int i = 0; i < sheetMergeCount; i++) {    
	        CellRangeAddress ca = sheet.getMergedRegion(i);    
	        int firstColumn = ca.getFirstColumn();    
	        int lastColumn = ca.getLastColumn();    
	        int firstRow = ca.getFirstRow();    
	        int lastRow = ca.getLastRow();
	        if(row >= firstRow && row <= lastRow && col >= firstColumn && col <= lastColumn){
                Row fRow = sheet.getRow(firstRow);    
                Cell fCell = fRow.getCell(firstColumn);    
                return getCellValue(fCell);
	        }
	    }
	    return null;    
	}
	
	private static <K, V> void setCellStyle(Workbook wb, Cell cell, ExcelCell<K, V> excelCell) {
		Style style = excelCell.getStyle();
		setCellStyle(
				wb, 
				cell, 
				style.getFontFamily(), 
				style.getFontSize(), 
				style.getFontBold(), 
				style.getFontColor(), 
				style.getAlignment(), 
				style.getVerticalAlignment(), 
				style.getBorderTop(), 
				style.getBorderRight(), 
				style.getBorderBottom(), 
				style.getBorderLeft(), 
				style.getBorderTopColor(), 
				style.getBorderRightColor(), 
				style.getBorderBottomColor(), 
				style.getBorderLeftColor(),
				style.getForegroundColor());
	}
	
	private static void setCellStyle(
			Workbook wb, 
			Cell cell, 
			String fontFamily, 
			Short fontSize, 
			Boolean fontBold, 
			Short fontColor,
			HorizontalAlignment alignment, 
			VerticalAlignment verticalAlignment, 
			BorderStyle borderTop,
			BorderStyle borderRight,
			BorderStyle borderBottom,
			BorderStyle borderLeft,
			Short borderTopColor,
			Short borderRightColor,
			Short borderBottomColor,
			Short borderLeftColor,
			Short foregroundColor
			) {
		String key = getCellStyleCacheKey(
				fontFamily, 
				fontSize, 
				fontBold, 
				fontColor,
				alignment, 
				verticalAlignment, 
				borderTop,
				borderRight,
				borderBottom,
				borderLeft,
				borderTopColor,
				borderRightColor,
				borderBottomColor,
				borderLeftColor,
				foregroundColor);
		Map<String, CellStyle> map = cellStyleCacheThreadLocal.get();
		if(map == null) {
			map = new HashMap<String, CellStyle>();
			cellStyleCacheThreadLocal.set(map);
		}
		CellStyle cellStyle = map.get(key);
		if(cellStyle == null) {
			cellStyle = wb.createCellStyle();
			Font font = wb.createFont();
			if(fontFamily == null) {
				fontFamily = FONT_FAMILY_SONG;
			}
			if(fontSize == null) {
				fontSize = DEFAULT_EXCEL_FONT_SIZE;
			}
			if(fontBold == null) {
				fontBold = FONT_BOLD_FALSE;
			}
			if(fontColor == null) {
				fontColor = HSSFColorPredefined.BLACK.getIndex();
			}
			if(alignment == null) {
				alignment = HorizontalAlignment.LEFT;
			}
			if(verticalAlignment == null) {
				verticalAlignment = VerticalAlignment.CENTER;
			}
			if(borderTop == null) {
				borderTop = BorderStyle.THIN;
			}
			if(borderRight == null) {
				borderRight = BorderStyle.THIN;
			}
			if(borderBottom == null) {
				borderBottom = BorderStyle.THIN;
			}
			if(borderLeft == null) {
				borderLeft = BorderStyle.THIN;
			}
			if(borderTopColor == null) {
				borderTopColor = HSSFColorPredefined.BLACK.getIndex();
			}
			if(borderRightColor == null) {
				borderRightColor = HSSFColorPredefined.BLACK.getIndex();
			}
			if(borderBottomColor == null) {
				borderBottomColor = HSSFColorPredefined.BLACK.getIndex();
			}
			if(borderLeftColor == null) {
				borderLeftColor = HSSFColorPredefined.BLACK.getIndex();
			}
			font.setFontName(fontFamily);
			font.setFontHeightInPoints(fontSize);
			font.setBold(fontBold);
			font.setColor(fontColor);
			cellStyle.setFont(font);
			cellStyle.setAlignment(alignment);
			cellStyle.setVerticalAlignment(verticalAlignment);
			cellStyle.setBorderTop(borderTop);
			cellStyle.setBorderRight(borderRight);
			cellStyle.setBorderBottom(borderBottom);
			cellStyle.setBorderLeft(borderLeft);
			cellStyle.setTopBorderColor(borderTopColor);
			cellStyle.setRightBorderColor(borderRightColor);
			cellStyle.setBottomBorderColor(borderBottomColor);
			cellStyle.setLeftBorderColor(borderLeftColor);
			if(foregroundColor != null) {
				cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
				cellStyle.setFillForegroundColor(foregroundColor);
			}
			map.put(key, cellStyle);
		}
		cell.setCellStyle(cellStyle);
	}
	
	private static String getCellStyleCacheKey(Object...keys) {
		StringBuffer sb = new StringBuffer();
		for(Object key: keys) {
			sb.append(key + "").append(SPLIT);
		}
		String cacheKeyStr = sb.toString();
		if(StringUtils.isNotBlank(cacheKeyStr)) {
			return CodeUtils.md5Hex(cacheKeyStr);
		}
		return null;
	}
	
	public static <K, V> byte[] createExcel(Map<String, Table<ExcelCell<K, V>>> tableMap) {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		createExcel(tableMap, out);
		byte[] bytes = out.toByteArray();
		IoUtils.close(out);
		return bytes;
	}
	
	public static <K, V> void createExcel(Map<String, Table<ExcelCell<K, V>>> tableMap, String path) {
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(FileUtils.buildFile(path));
			createExcel(tableMap, out);
		} catch (FileNotFoundException e) {
		    LOG.error(e);
		} finally {
			IoUtils.close(out);
		}
	}
	
	public static <K, V> void createExcel(Map<String, Table<ExcelCell<K, V>>> tableMap, OutputStream out) {
		if(MapUtils.isNotBlank(tableMap)) {
			Workbook workbook = new XSSFWorkbook();
			Iterator<Entry<String, Table<ExcelCell<K, V>>>> it = tableMap.entrySet().iterator();
			while(it.hasNext()) {
				Map<String, ExcelCell<K, V>> mergeCacheMap = new LinkedHashMap<>();
				Entry<String, Table<ExcelCell<K, V>>> entry = it.next();
				Sheet sheet = workbook.createSheet(entry.getKey());
				Table<ExcelCell<K, V>> table = entry.getValue();
				if(table != null) {
					List<List<ExcelCell<K, V>>> ecTable = table.getTable();
					if(CollectionUtils.isNotBlank(ecTable)) {
						int rowSize = ecTable.size();
						for(int row = 0; row < ecTable.size(); row ++) {
							Short maxRowHeight = null;
							Row poiRow = getRow(sheet, row);
							List<ExcelCell<K, V>> rowList = ecTable.get(row);
							if(CollectionUtils.isNotBlank(rowList)) {
								int colSize = rowList.size();
								for(int col = 0; col < colSize; col ++) {
									if(row == 0) {
										// 设置列宽
										Short maxColWidth = null;
										for(int rowBuf = 0; rowBuf < rowSize; rowBuf ++) {
											ExcelCell<K, V> ecBuf = ecTable.get(rowBuf).get(col);
											if(ecBuf != null) {
												Short width = ecBuf.getStyle().getWidth();
												if(width != null) {
													if(maxColWidth == null) {
														maxColWidth = width;
													} else if(width > maxColWidth) {
														maxColWidth = width;
													}
												}
											}
										}
										if(maxColWidth != null) {
											sheet.setColumnWidth(col, maxColWidth);
										}
									}
									ExcelCell<K, V> ec = rowList.get(col);
									if(ec != null) {
										Short height = ec.getStyle().getHeight();
										if(height != null) {
											if(maxRowHeight == null) {
												maxRowHeight = height;
											} else if(height > maxRowHeight) {
												maxRowHeight = height;
											}
										}
										K key = ec.getKey();
										V value = ec.getValue();
										Cell cell = getCell(poiRow, col);
										fillSheet(sheet, poiRow, cell, value);
										setCellStyle(workbook, cell, ec);
										int lastRow = row;
										int lastCol = col;
										boolean shouldMerge = key != null;
										if(shouldMerge) {
											Set<String> mergeCacheKeySet = mergeCacheMap.keySet();
											for(String mergeCacheKey: mergeCacheKeySet) {
												String[] areaStr = mergeCacheKey.split(SPLIT);
												if(row >= DataTypeUtils.stringToInteger(areaStr[0]) && row <= DataTypeUtils.stringToInteger(areaStr[1]) && col >= DataTypeUtils.stringToInteger(areaStr[2]) && col <= DataTypeUtils.stringToInteger(areaStr[3])) {
													shouldMerge = false;
													break;
												}
											}
										}
										if(shouldMerge) {
											for(int rowBuf = row + 1; rowBuf < rowSize; rowBuf ++) {
												ExcelCell<K, V> ecBuf = ecTable.get(rowBuf).get(col);
												if(ecBuf != null) {
													K bufKey = ecBuf.getKey();
													if(!ObjectUtils.equals(key, bufKey)) {
														break;
													}
													lastRow ++; 
												}
											}
											for(int colBuf = col + 1; colBuf < colSize; colBuf ++) {
												ExcelCell<K, V> ecBuf = ecTable.get(row).get(colBuf);
												if(ecBuf != null) {
													K bufKey = ecBuf.getKey();
													if(!ObjectUtils.equals(key, bufKey)) {
														break;
													}
													lastCol ++; 
												}
											}
											if(lastRow >  row || lastCol > col) {
												String mergeCacheKey = row + SPLIT + lastRow + SPLIT + col + SPLIT + lastCol;
												mergeCacheMap.put(mergeCacheKey, ec);
											}
										}
									}
								}
							}
							if(maxRowHeight != null) {
								poiRow.setHeight(maxRowHeight);
							}
						}
						Iterator<Entry<String, ExcelCell<K, V>>> mergeCacheIt = mergeCacheMap.entrySet().iterator();
						while(mergeCacheIt.hasNext()) {
							Entry<String, ExcelCell<K, V>> mergeCacheEntry = mergeCacheIt.next();
							String mergeCacheKey = mergeCacheEntry.getKey();
							String[] areaStr = mergeCacheKey.split(SPLIT);
							mergeGrid(DataTypeUtils.stringToInteger(areaStr[0]), DataTypeUtils.stringToInteger(areaStr[1]), DataTypeUtils.stringToInteger(areaStr[2]), DataTypeUtils.stringToInteger(areaStr[3]), mergeCacheEntry.getValue(), sheet, workbook);
						}
					}
				}
			}
			try {
				workbook.write(out);
			} catch (IOException e) {
			    LOG.error(e);
			}
		}
	}
	
	/**
	 * 表格模型
	 * 
	 * @author fengwk
	 * @version 创建时间:2017年6月15日 下午7:05:56
	 */
	public static class Table<T> implements Serializable {
		
		private static final long serialVersionUID = 4842439350928886215L;
		
		private List<List<T>> table = new ArrayList<List<T>>();

		/**
		 * 获取
		 * 
		 * @param row
		 * @param col
		 */
		public T get(int row, int col) {
			return table.get(row).get(col);
		}

		/**
		 * 添加表格
		 * 
		 * @param row
		 *            行
		 * @param col
		 *            列
		 */
		public void add(int row, int col, T content) {
			List<T> rowList = null;
			try {
				rowList = table.get(row);
			} catch (Exception e) {
				rowList = new ArrayList<T>();
				CollectionUtils.fillList(row, table, rowList, List.class);
			}
			CollectionUtils.fillList(col, rowList, content, null);
		}

		/**
		 * 行数
		 * 
		 * @return
		 */
		public int rowSize() {
			return table.size();
		}

		/**
		 * 列数
		 * 
		 * @return
		 */
		public int colSize() {
			List<T> colList = null;
			try {
				colList = table.get(0);
			} catch (Exception e) {
				return -1;
			}
			return colList.size();
		}

		/**
		 * 获取表格
		 * 
		 * @return
		 */
		public List<List<T>> getTable() {
			int maxRowSize = 0;
			for (List<T> rowList : table) {
				int nowSize = rowList.size() - 1;
				if (nowSize > maxRowSize) {
					maxRowSize = nowSize;
				}
			}
			for (List<T> rowList : table) {
				CollectionUtils.fillList(maxRowSize + 1, rowList, null, null);
				rowList.remove(maxRowSize + 1);
			}
			return table;
		}

		/**
		 * 输出表格
		 */
		public void print() {
			for (List<T> rowList : getTable()) {
				for (T col : rowList) {
					System.out.print(col + "");
				}
				System.out.println("");
			}
		}
	}
	
	/**
	 * Excel单元格模型
	 * 
	 * @author fengwk
	 * @version 创建时间:2017年6月15日 下午7:05:41
	 */
	public static class ExcelGrid implements Serializable {

		private static final long serialVersionUID = -263253719880486097L;

		private ExcelGrid root;// 根节点单元格
		private Integer row;// 行数,0开始
		private Integer col;// 列数,0开始
		private String value;// 包含的值

		private static ExcelGrid build(ExcelGrid root, Integer row, Integer col, String value) {
			ExcelGrid excelGrid = new ExcelGrid();
			excelGrid.setRoot(root);
			excelGrid.setRow(row);
			excelGrid.setCol(col);
			excelGrid.setValue(value);
			return excelGrid;
		}

		private ExcelGrid() {
		}

		public ExcelGrid getRoot() {
			return root;
		}

		public void setRoot(ExcelGrid root) {
			this.root = root;
		}

		public Integer getRow() {
			return row;
		}

		public void setRow(Integer row) {
			this.row = row;
		}

		public Integer getCol() {
			return col;
		}

		public void setCol(Integer col) {
			this.col = col;
		}

		public String getValue() {
			return value;
		}

		public void setValue(String value) {
			this.value = value;
		}
	}
	
	/**
	 * 单元格对象
	 * 
	 * @author fengwk
	 *
	 * @param <K>
	 * @param <V>
	 */
	public static class ExcelCell<K, V> implements Serializable {

		private static final long serialVersionUID = -1132943320476939161L;
		
		private K key;// 用于表示相同单元格,为null时不计其标识功能
		private V value;// 单元格内容
		private Style style;

		public static <K, V> ExcelCell<K, V> build(K k, V v) {
			ExcelCell<K, V> ec = new ExcelCell<>();
			ec.setKey(k);
			ec.setValue(v);
			return ec;
		}
		
		public static <K, V> ExcelCell<K, V> build(K k, V v, Style style) {
			ExcelCell<K, V> ec = build(k, v, style);
			ec.setStyle(style);
			return ec;
		}
		
		public K getKey() {
			return key;
		}

		public ExcelCell<K, V> setKey(K key) {
			this.key = key;
			return this;
		}

		public V getValue() {
			return value;
		}

		public ExcelCell<K, V> setValue(V value) {
			this.value = value;
			return this;
		}

		public Style getStyle() {
			if (style == null) {
				style = Style.build();
			}
			return style;
		}

		public ExcelCell<K, V> setStyle(Style style) {
			this.style = style;
			return this;
		}
		
		/**
		 * 渲染对象,用于渲染单元格样式
		 * 
		 * @author fengwk
		 *
		 */
		public static class Style implements Serializable {

			private static final long serialVersionUID = 7166470161353834895L;

			private Short height;
			private Short width;
			private String fontFamily;
			private Short fontSize;
			private Boolean fontBold;
			private Short fontColor;
			private HorizontalAlignment alignment;
			private VerticalAlignment verticalAlignment;
			private BorderStyle borderTop;
			private BorderStyle borderRight;
			private BorderStyle borderBottom;
			private BorderStyle borderLeft;
			private BorderStyle border;
			private Short borderTopColor;
			private Short borderRightColor;
			private Short borderBottomColor;
			private Short borderLeftColor;
			private Short borderColor;
			private Short foregroundColor;

			public static Style build() {
				return new Style();
			}

			public Short getHeight() {
				return height;
			}

			public Style setHeight(Short height) {
				this.height = height;
				return this;
			}

			public Short getWidth() {
				return width;
			}

			public Style setWidth(Short width) {
				this.width = width;
				return this;
			}

			public String getFontFamily() {
				return fontFamily;
			}

			public Style setFontFamily(String fontFamily) {
				this.fontFamily = fontFamily;
				return this;
			}

			public Short getFontSize() {
				return fontSize;
			}

			public Style setFontSize(Short fontSize) {
				this.fontSize = fontSize;
				return this;
			}

			public Boolean getFontBold() {
				return fontBold;
			}

			public Style setFontBold(Boolean fontBold) {
				this.fontBold = fontBold;
				return this;
			}

			public Short getFontColor() {
				return fontColor;
			}

			public Style setFontColor(Short fontColor) {
				this.fontColor = fontColor;
				return this;
			}

			public HorizontalAlignment getAlignment() {
				return alignment;
			}

			public Style setAlignment(HorizontalAlignment alignment) {
				this.alignment = alignment;
				return this;
			}

			public VerticalAlignment getVerticalAlignment() {
				return verticalAlignment;
			}

			public Style setVerticalAlignment(VerticalAlignment verticalAlignment) {
				this.verticalAlignment = verticalAlignment;
				return this;
			}

			public BorderStyle getBorderTop() {
				return getBorderStyleByBorder(borderTop);
			}

			public Style setBorderTop(BorderStyle borderTop) {
				this.borderTop = borderTop;
				return this;
			}

			public BorderStyle getBorderRight() {
				return getBorderStyleByBorder(borderRight);
			}

			public Style setBorderRight(BorderStyle borderRight) {
				this.borderRight = borderRight;
				return this;
			}

			public BorderStyle getBorderBottom() {
				return getBorderStyleByBorder(borderBottom);
			}

			public Style setBorderBottom(BorderStyle borderBottom) {
				this.borderBottom = borderBottom;
				return this;
			}

			public BorderStyle getBorderLeft() {
				return getBorderStyleByBorder(borderLeft);
			}

			public Style setBorderLeft(BorderStyle borderLeft) {
				this.borderLeft = borderLeft;
				return this;
			}

			public BorderStyle getBorder() {
				return border;
			}

			public Style setBorder(BorderStyle border) {
				this.border = border;
				return this;
			}

			public Short getBorderTopColor() {
				return getBorderColorByBorderColor(borderTopColor);
			}

			public Style setBorderTopColor(Short borderTopColor) {
				this.borderTopColor = borderTopColor;
				return this;
			}

			public Short getBorderRightColor() {
				return getBorderColorByBorderColor(borderRightColor);
			}

			public Style setBorderRightColor(Short borderRightColor) {
				this.borderRightColor = borderRightColor;
				return this;
			}

			public Short getBorderBottomColor() {
				return getBorderColorByBorderColor(borderBottomColor);
			}

			public Style setBorderBottomColor(Short borderBottomColor) {
				this.borderBottomColor = borderBottomColor;
				return this;
			}

			public Short getBorderLeftColor() {
				return getBorderColorByBorderColor(borderLeftColor);
			}

			public Style setBorderLeftColor(Short borderLeftColor) {
				this.borderLeftColor = borderLeftColor;
				return this;
			}

			public Short getBorderColor() {
				return borderColor;
			}

			public Style setBorderColor(Short borderColor) {
				this.borderColor = borderColor;
				return this;
			}

			public Short getForegroundColor() {
				return foregroundColor;
			}

			public Style setForegroundColor(Short foregroundColor) {
				this.foregroundColor = foregroundColor;
				return this;
			}

			private BorderStyle getBorderStyleByBorder(BorderStyle borderStyle) {
				if (borderStyle == null) {
					return getBorder();
				}
				return borderStyle;
			}

			private Short getBorderColorByBorderColor(Short borderColor) {
				if (borderColor == null) {
					return getBorderColor();
				}
				return borderColor;
			}

		}

	}
	
}
